Using Net Change to Perform a Full Refresh on Large Files

Kourier Integrator Online Help

This procedure applies to U2 to SQL exports.
Most exports on large files have some sort of date that can be used to partition the file into two or more logical pieces.
All exports for a source U2 file are implemented as one service and that service is only for that source U2 file.
This document assumes that a cut-off date has been implemented via an integration property so one change to that property will effect both the parent and child exports.
The example documented in this procedure uses a file named LEDGER which contains 10 million records with dates ranging from 01-01-1999 thru today. Our cut-off date integration property has a date of 6-20-2010. Our service is named ODS_LEDGER and it contains 5 exports.

Remove the ODS_LEDGER service from any started schedule.
Modify the cut-off date property so that each export in the service extracts about 1 years’ worth of data (e.g. DATE >= “01-01-2015”).
Manually run the ODS_LEDGER service in full-refresh mode.

Use the KCL.MAKEBATCH command to select all records from the original cut-off date of 06-20-2010 thru the temporary cut-off date of 01-01-2015.
Note: you must include any other criteria that you are using in your parent export (e.g. AND WITH @ID = “S]”) when running the KCL.MAKEBATCH command. For example:
1. KCL.MAKEBATCH LEDGER WITH DATE >= “6-20-2010” AND WITH DATE < “01-01-2015” BY-DSND DATE
  1. This will create an ordered list of item-ids from the LEDGER file in reverse chronological order so that the most recently changed records will be processed before the oldest records prior to the cut-off date are processed.
  2. You can control the size of each batch by specifying the BATCH-SIZE keyword followed by a number. If not specified, each batch will contain 20,000 records.
Once the ODS_LEDGER service has completed, reset the cut-off property to its original value of 6-20-2010.

There are two options available to process the batches just created:

Modify the ODS_LEDGER service to run the following TCL command before running the KMK.EXPORT command: KMK.CMD “KCL.MOVEBATCH LEDGER” NO-MESSAGE. The KCL.MOVEBATCH command forces the next batch of item-ids for the LEDGER file into net change so that they will be processed with any other records that might have changed in the LEDGER file since the last time the service was run in net change mode. The service might look something like this:

Remember that multiple commands in a service are separated by the pipe (|) character.

Every time KCL.MOVEBATCH runs, it will log event number KMK-608 in the Event Log.

When the service has processed all batches, you can remove the KMK.CMD “KCL.MOVEBATCH LEDGER” NO-MESSAGE line from the service (remember to remove the pipe (|) character too). Note: You can setup and Event Notification Filter for event KMK-609 which is logged when all batches for the service have been processed.

Create a service that contains the following TCL command: KMK.CMD “KCL.MOVEBATCH LEDGER” NO-MESSAGE. The service might look something like this:

Create a schedule that runs this new service on the same frequency as the ODS_LEDGER service but only during times that are outside your peak processing hours. For example, if you run the ODS_LEDGER service from 2:00am thru 10:00pm every 15 minutes and your peak hours are from 8:00am to 5:00pm, setup this new schedule to run between 2:00am and 8:00am every 15 minutes and between 5:00pm and 10:00pm every 15 minutes.

You can setup an Event Notification Filter for event KMK-609 which is logged when all batches for the service have been processed.

Every time KCL.MOVEBATCH runs, it will log event number KMK.608 in the Event Log.